package com.luntan.school.dao.impl;

import com.luntan.school.dao.AdminDao;
import com.luntan.school.dao.PostsDao;
import com.luntan.school.model.Admin;
import com.luntan.school.service.Posts;
import com.luntan.school.util.DBUtil;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class PostsDaoImpl implements PostsDao {
    @Override
    public List<Posts> getPostsByCategory(Integer categoryId) {
        try {
            //获取连接
            Connection con = DBUtil.getConnection();

            //执行查询并返回结果集
            PreparedStatement pst = con.prepareStatement("select * from posts where category_id = ?");
            pst.setInt(1, categoryId); //填充参数，注意参数编号从1开始
            ResultSet rs = pst.executeQuery();

            //封装结果为列表数据
            List<Posts> postsList = new ArrayList<>();
            while (rs.next()) {
                Posts posts = new Posts();

                posts.setId(rs.getInt("id"));
                posts.setTitle(rs.getString("title"));
                posts.setContent(rs.getString("content"));
                posts.setImg(rs.getString("img"));
                posts.setPubDate(rs.getTimestamp("pubdate"));
                posts.setStick(rs.getBoolean("stick"));
                posts.setClickCount(rs.getInt("click_count"));
                posts.setLikeNum(rs.getInt("likeNum"));
                posts.setDislikeNum(rs.getInt("dislikeNum"));

                /**
                 * 获取编辑信息（先获取编号，然后根据编号获取全部信息）
                 */
                int editorId = rs.getInt("editor");
                AdminDao adminDao = new AdminDaoImpl();
                Admin editor = adminDao.getAdminById(editorId);
                posts.setEditor(editor);

                posts.setCommentNum(this.getCommentNumByPostsId(posts.getId())); // 关联查询

                postsList.add(posts);
            }

            //释放连接资源
            rs.close();
            pst.close();
            con.close();

            //返回结果
            return postsList;
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return null;
    }

    @Override
    public List<Posts> getPostsByTag(Integer tagId) {
        try {
            //获取连接
            Connection con = DBUtil.getConnection();

            //执行查询并返回结果集
            PreparedStatement pst = con.prepareStatement("select * from posts inner join posts_tag on posts.id=posts_tag.posts_id where tag_id = ?");
            pst.setInt(1, tagId);
            ResultSet rs = pst.executeQuery();

            //封装结果为列表数据
            List<Posts> postsList = new ArrayList<>();
            while (rs.next()) {
                Posts posts = new Posts();

                posts.setId(rs.getInt("id"));
                posts.setTitle(rs.getString("title"));
                posts.setContent(rs.getString("content"));
                posts.setImg(rs.getString("img"));
                posts.setPubDate(rs.getTimestamp("pubdate"));
                posts.setStick(rs.getBoolean("stick"));
                posts.setClickCount(rs.getInt("click_count"));
                posts.setLikeNum(rs.getInt("likeNum"));
                posts.setDislikeNum(rs.getInt("dislikeNum"));

                /**
                 * 获取编辑信息（先获取编号，然后根据编号获取全部信息）
                 */
                int editorId = rs.getInt("editor");
                AdminDao adminDao = new AdminDaoImpl();
                Admin editor = adminDao.getAdminById(editorId);
                posts.setEditor(editor);

                posts.setCommentNum(this.getCommentNumByPostsId(posts.getId())); // 关联查询

                postsList.add(posts);
            }

            //释放连接资源
            rs.close();
            pst.close();
            con.close();

            //返回结果
            return postsList;
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return null;
    }

    /**
     * 获取帖子评论数
     *
     * @param postsId
     * @return
     */
    private Integer getCommentNumByPostsId(int postsId) {
        try {
            //获取连接
            Connection con = DBUtil.getConnection();

            //执行查询并返回结果集
            PreparedStatement pst = con.prepareStatement("select count(*) from comment WHERE posts_id =?");
            pst.setInt(1, postsId);

            ResultSet rs = pst.executeQuery();
            if (rs.next()) {
                int num = rs.getInt(1);

                //释放连接资源
                rs.close();
                pst.close();
                con.close();

                return num;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return 0;
    }

    @Override
    public List<Posts> getStickPosts(int limit) {
        try {
            //获取连接
            Connection con = DBUtil.getConnection();

            //执行查询并返回结果集
            PreparedStatement pst = con.prepareStatement("select * from posts where stick = ? limit ?");
            pst.setBoolean(1, true);
            pst.setInt(2, limit);
            ResultSet rs = pst.executeQuery();

            //封装结果为列表数据
            List<Posts> postsList = new ArrayList<>();
            while (rs.next()) {
                Posts posts = new Posts();

                posts.setId(rs.getInt("id"));
                posts.setTitle(rs.getString("title"));
                posts.setContent(rs.getString("content"));
                posts.setImg(rs.getString("img"));
                posts.setPubDate(rs.getTimestamp("pubdate"));
                posts.setStick(rs.getBoolean("stick"));
                posts.setClickCount(rs.getInt("click_count"));
                posts.setLikeNum(rs.getInt("likeNum"));
                posts.setDislikeNum(rs.getInt("dislikeNum"));

                /**
                 * 获取编辑信息（先获取编号，然后根据编号获取全部信息）
                 */
                int editorId = rs.getInt("editor");
                AdminDao adminDao = new AdminDaoImpl();
                Admin editor = adminDao.getAdminById(editorId);
                posts.setEditor(editor);

                posts.setCommentNum(this.getCommentNumByPostsId(posts.getId())); // 关联查询

                postsList.add(posts);
            }

            //释放连接资源
            rs.close();
            pst.close();
            con.close();

            //返回结果
            return postsList;
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return null;
    }

    @Override
    public List<Posts> getAllPosts() {
        try {
            //获取连接
            Connection con = DBUtil.getConnection();

            //执行查询并返回结果集
            Statement st = con.createStatement();
            ResultSet rs = st.executeQuery("select * from posts");
            //封装结果为列表数据
            List<Posts> postsList = new ArrayList<>();
            while (rs.next()) {
                Posts posts = new Posts();

                posts.setId(rs.getInt("id"));
                posts.setTitle(rs.getString("title"));
                posts.setContent(rs.getString("content"));
                posts.setImg(rs.getString("img"));
                posts.setPubDate(rs.getTimestamp("pubdate"));
                posts.setClickCount(rs.getInt("click_count"));
                posts.setStick(rs.getBoolean("stick"));
                posts.setLikeNum(rs.getInt("likeNum"));
                posts.setDislikeNum(rs.getInt("dislikeNum"));

                /**
                 * 获取编辑信息（先获取编号，然后根据编号获取全部信息）
                 */
                int editorId = rs.getInt("editor");
                AdminDao adminDao = new AdminDaoImpl();
                Admin editor = adminDao.getAdminById(editorId);
                posts.setEditor(editor);

                posts.setCommentNum(this.getCommentNumByPostsId(posts.getId())); // 关联查询

                postsList.add(posts);
            }

            //释放连接资源
            rs.close();
            st.close();
            con.close();

            //返回结果
            return postsList;
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return null;
    }

    @Override
    public Integer addPosts(Posts posts) {
        try {
            //获取连接
            Connection con = DBUtil.getConnection();

            //执行查询并返回结果集
            PreparedStatement pst = con.prepareStatement("insert into news(title, content, pubdate, category_id, img, editor,stick,click_count,likeNum,dislikeNum) values(?, ?, ?, ?, ?, ?, ?, ? ,? ,?)", Statement.RETURN_GENERATED_KEYS);
            pst.setString(1, posts.getTitle());
            pst.setString(2, posts.getContent());
            pst.setTimestamp(3, new Timestamp(new java.util.Date().getTime())); //当前时间作为发布时间
            pst.setInt(4, posts.getCategory().getId());
            pst.setString(5, posts.getImg());
            pst.setInt(6, posts.getEditor().getId());
            pst.setBoolean(7, posts.getStick());
            pst.setInt(8, 0);
            pst.setInt(9,0);
            pst.setInt(10,0);

            pst.executeUpdate();
            ResultSet rs = pst.getGeneratedKeys();
            rs.next();
            Integer id = rs.getInt(1);

            //释放连接资源
            rs.close();
            pst.close();
            con.close();

            return id;
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return -1; //新增失败
    }

    @Override
    public void updatePosts(Posts posts) {
        try {
            //获取连接
            Connection con = DBUtil.getConnection();

            //执行查询并返回结果集
            PreparedStatement pst = con.prepareStatement("update posts set title=?, content=?, category_id =?, editor=?, stick=?"
                    + (posts.getImg() != null ? ", img=? " : "")
                    + " where id=?");
            pst.setString(1, posts.getTitle());
            pst.setString(2, posts.getContent());
            pst.setInt(3, posts.getCategory().getId());
            pst.setInt(4, posts.getEditor().getId());
            pst.setBoolean(5, posts.getStick());
            if (posts.getImg() != null) {
                pst.setString(6, posts.getImg());
                pst.setInt(7, posts.getId());
            } else {
                pst.setInt(6, posts.getId());
            }

            pst.executeUpdate();

            //释放连接资源
            pst.close();
            con.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    @Override
    public Posts getPostsById(int postsId) {
        try {
            //获取连接
            Connection con = DBUtil.getConnection();

            //执行查询并返回结果集
            PreparedStatement pst = con.prepareStatement("select * from posts where id = ?");
            pst.setInt(1, postsId); //填充参数，注意参数编号从1开始
            ResultSet rs = pst.executeQuery();

            Posts posts = new Posts();
            if (rs.next()) {
                posts.setId(rs.getInt("id"));
                posts.setTitle(rs.getString("title"));
                posts.setContent(rs.getString("content"));
                posts.setPubDate(rs.getTimestamp("pubdate"));
                posts.setStick(rs.getBoolean("stick"));
                posts.setClickCount(rs.getInt("click_count"));
                posts.setLikeNum(rs.getInt("likeNum"));
                posts.setDislikeNum(rs.getInt("dislikeNum"));

                /**
                 * 获取编辑信息（先获取编号，然后根据编号获取全部信息）
                 */
                int editorId = rs.getInt("editor");
                AdminDao adminDao = new AdminDaoImpl();
                Admin editor = adminDao.getAdminById(editorId);
                posts.setEditor(editor);

                posts.setCommentNum(this.getCommentNumByPostsId(posts.getId())); // 关联查询
            }

            //释放连接资源
            rs.close();
            pst.close();
            con.close();

            //返回结果
            return posts;
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return null;
    }

    @Override
    public List<Posts> getHotPosts() {
        try {
            //获取连接
            Connection con = DBUtil.getConnection();

            //执行查询并返回结果集
            Statement st = con.createStatement();
            ResultSet rs = st.executeQuery("select * from posts order by pubdate desc limit 6");
            //封装结果为列表数据
            List<Posts> postsList = new ArrayList<>();
            while (rs.next()) {
                Posts posts = new Posts();

                posts.setId(rs.getInt("id"));
                posts.setTitle(rs.getString("title"));
                posts.setContent(rs.getString("content"));
                posts.setImg(rs.getString("img"));
                posts.setPubDate(rs.getTimestamp("pubdate"));
                posts.setStick(rs.getBoolean("stick"));
                posts.setClickCount(rs.getInt("click_count"));
                posts.setLikeNum(rs.getInt("likeNum"));
                posts.setDislikeNum(rs.getInt("dislikeNum"));

                /**
                 * 获取编辑信息（先获取编号，然后根据编号获取全部信息）
                 */
                int editorId = rs.getInt("editor");
                AdminDao adminDao = new AdminDaoImpl();
                Admin editor = adminDao.getAdminById(editorId);
                posts.setEditor(editor);

                posts.setCommentNum(this.getCommentNumByPostsId(posts.getId())); // 关联查询

                postsList.add(posts);
            }

            //释放连接资源
            rs.close();
            st.close();
            con.close();

            //返回结果
            return postsList;
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return null;
    }

    @Override
    public List<Posts> getRecommendedPosts(int postsId) {
        try {
            //获取连接
            Connection con = DBUtil.getConnection();

            //执行查询并返回结果集
            Statement st = con.createStatement();
            ResultSet rs = st.executeQuery("select * from posts order by rand() limit 5");
            //封装结果为列表数据
            List<Posts> postsList = new ArrayList<>();
            while (rs.next()) {
                Posts posts = new Posts();

                posts.setId(rs.getInt("id"));
                posts.setTitle(rs.getString("title"));
                posts.setContent(rs.getString("content"));
                posts.setImg(rs.getString("img"));
                posts.setPubDate(rs.getTimestamp("pubdate"));
                posts.setStick(rs.getBoolean("stick"));
                posts.setClickCount(rs.getInt("click_count"));
                posts.setLikeNum(rs.getInt("likeNum"));
                posts.setDislikeNum(rs.getInt("dislikeNum"));

                /**
                 * 获取编辑信息（先获取编号，然后根据编号获取全部信息）
                 */
                int editorId = rs.getInt("editor");
                AdminDao adminDao = new AdminDaoImpl();
                Admin editor = adminDao.getAdminById(editorId);
                posts.setEditor(editor);

                posts.setCommentNum(this.getCommentNumByPostsId(posts.getId())); // 关联查询

                postsList.add(posts);
            }

            //释放连接资源
            rs.close();
            st.close();
            con.close();

            //返回结果
            return postsList;
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return null;
    }

    @Override
    public void updateClickCount(int postsId) {
        try {
            //获取连接
            Connection con = DBUtil.getConnection();
            PreparedStatement pst = con.prepareStatement("update posts set click_count=click_count+1 where id=?");
            pst.setInt(1, postsId);

            pst.executeUpdate();

            //释放连接资源
            pst.close();
            con.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    @Override
    public List<Map<String, Object>> statPostsCountByCategory() {
        //封装结果为列表数据
        List<Map<String, Object>> statsList = new ArrayList<>();
        try {
            //获取连接
            Connection con = DBUtil.getConnection();

            //执行查询并返回结果集
            Statement st = con.createStatement();
            ResultSet rs = st.executeQuery("select id, name, IFNULL(count, 0) as count from category left JOIN (SELECT category_id, count(*) as count from posts GROUP BY category_id) as posts on posts.category_id=category.id");

            while (rs.next()) {
                Map<String, Object> row = new HashMap<>();
                row.put("id", rs.getInt("id"));
                row.put("name", rs.getString("name"));
                row.put("count", rs.getInt("count"));

                statsList.add(row);
            }

            //释放连接资源
            rs.close();
            st.close();
            con.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return statsList;
    }

    @Override
    public List<Map<String, Object>> statPostsCountByTag() {
        //封装结果为列表数据
        List<Map<String, Object>> statsList = new ArrayList<>();
        try {
            //获取连接
            Connection con = DBUtil.getConnection();

            //执行查询并返回结果集
            Statement st = con.createStatement();
            ResultSet rs = st.executeQuery("select tag.id as id, name, count(*) as count from posts left join posts_tag on posts.id=posts_tag.posts_id left join tag on posts_tag.tag_id=tag.id group by name");

            while (rs.next()) {
                Map<String, Object> row = new HashMap<>();
                row.put("id", rs.getInt("id"));
                row.put("name", rs.getString("name"));
                row.put("count", rs.getInt("count"));

                statsList.add(row);
            }

            //释放连接资源
            rs.close();
            st.close();
            con.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        //返回结果
        return statsList;
    }

    @Override
    public List<Map<String, Object>> statPostsCommentCountByDate() {
        //封装结果为列表数据
        List<Map<String, Object>> statsList = new ArrayList<>();
        try {
            //获取连接
            Connection con = DBUtil.getConnection();

            Statement st = con.createStatement();
            ResultSet rs = st.executeQuery("select date_format(max(pubdate), '%Y-%m-%d') from `comment`"); // 查询结束日期（字串符串格式）
            rs.next();
            String endDate = rs.getString(1);

            rs = st.executeQuery("select DATEDIFF(max(pubdate),min(pubdate)) from `comment`"); // 查询日期间隔时长（天数）
            rs.next();
            Integer days = rs.getInt(1);
            days = days>699? 699: days; // 因mysql.help_topic表长度限制，最大天数不能超过699

            PreparedStatement pst = con.prepareStatement("select t1.date as date, ifnull(t2.count,0) as count from " +
                    "(select date_format(date_add(?,interval -t.help_topic_id day),'%Y-%m-%d') as date from mysql.help_topic t where t.help_topic_id<=?) t1 " +
                    "left join (select date_format(pubdate, '%Y-%m-%d') as date, count(*) as count from `comment` group by date order by pubdate) t2 " +
                    "on t1.date = t2.date order by date");
            pst.setString(1, endDate);
            pst.setInt(2, days);

            rs = pst.executeQuery();

            while (rs.next()) {
                Map<String, Object> row = new HashMap<>();
                row.put("date", rs.getString("date"));
                row.put("count", rs.getInt("count"));

                statsList.add(row);
            }

            //释放连接资源
            rs.close();
            st.close();
            con.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return statsList;
    }

    @Override
    public List<Map<String, Object>> statPostsCommentCountByUser() {
        //封装结果为列表数据
        List<Map<String, Object>> statsList = new ArrayList<>();

        try {
            //获取连接
            Connection con = DBUtil.getConnection();

            //执行查询并返回结果集
            Statement st = con.createStatement();
            ResultSet rs = st.executeQuery("select nickname, count(`comment`.id) as count from user left join `comment`  on `comment`.creator=user.id group by nickname order by count desc");

            while (rs.next()) {
                Map<String, Object> row = new HashMap<>();
                row.put("nickname", rs.getString("nickname"));
                row.put("count", rs.getInt("count"));

                statsList.add(row);
            }

            //释放连接资源
            rs.close();
            st.close();
            con.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return statsList;
    }

    @Override
    public PageImpl<Posts> getPagedPosts(int pageNumer, int pageSize, String key) {
        // 校正参数
        if (pageNumer < 1) {
            pageNumer = 1;
        }
        if (pageSize < 1) {
            pageSize = 1;
        }

        // 初始化分页对象
        PageImpl<Posts> page = new PageImpl<>();
        page.setPageNumber(pageNumer);
        page.setPageSize(pageSize);


        try {
            //获取连接
            Connection con = DBUtil.getConnection();

            // 查询总行数
            Statement st = con.createStatement();
            ResultSet rs = st.executeQuery("select  count(*) from posts" + (key==null|| key.equals("") ? "" : " where title like '%"+key+"%'"));
            if (rs.next()) {
                page.setTotalRows(rs.getInt(1)); //获取并设置总记录行数

                //执行分页查询
                PreparedStatement pst = con.prepareStatement("select * from posts"
                        + (key==null||key.equals("") ? "" : " where title like '%"+key+"%'") +" limit ?, ?");
                pst.setInt(1, (pageNumer - 1) * pageSize); // 设置limit的offset（起始行偏移值，0表达第1行）参数
                pst.setInt(2, pageSize); // 设置limit的count（最大返回行数）参数

                rs = pst.executeQuery();

                //封装结果为列表数据
                List<Posts> pagedData = new ArrayList<>();
                while (rs.next()) {
                    Posts posts = new Posts();

                    posts.setId(rs.getInt("id"));
                    posts.setTitle(rs.getString("title"));
                    posts.setContent(rs.getString("content"));
                    posts.setImg(rs.getString("img"));
                    posts.setPubDate(rs.getTimestamp("pubdate"));
                    posts.setStick(rs.getBoolean("stick"));
                    posts.setClickCount(rs.getInt("click_count"));
                    posts.setLikeNum(rs.getInt("likeNum"));
                    posts.setDislikeNum(rs.getInt("dislikeNum"));

                    /**
                     * 获取编辑信息（先获取编号，然后根据编号获取全部信息）
                     */
                    int editorId = rs.getInt("editor");
                    AdminDao adminDao = new AdminDaoImpl();
                    Admin editor = adminDao.getAdminById(editorId);
                    posts.setEditor(editor);

                    posts.setCommentNum(this.getCommentNumByPostsId(posts.getId())); // 关联查询

                    pagedData.add(posts);
                }
                page.setPagedData(pagedData); //设置分页查询结果数据

                //释放连接资源
                pst.close();
                rs.close();
                st.close();
                con.close();
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }

        return page;
    }


    public static void main(String[] args) {
        PostsDao postsDao = new PostsDaoImpl();
        System.out.println(postsDao.getPostsByCategory(10001));
        System.out.println(postsDao.getStickPosts(5));
        postsDao.updateClickCount(1);
    }
}
